package com.application.model.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.commons.lang.StringEscapeUtils;
import org.apache.log4j.Logger;

import com.application.model.exceptions.SQLExceptionNoChange;

public abstract class ManagerDataBase {

	private String 		url = "jdbc:mysql://localhost:3306/";
//	private String 		dbName = "BCWeb";
	private String 		dbName = "AulaWeb";
	private String 		driver = "com.mysql.jdbc.Driver";
	private String 		userName = "root"; 
	private String 		password = "root";

	protected Logger	logger;
	
	public ManagerDataBase() {
		try {
			logger = Logger.getLogger(getClass());
			Class.forName(driver).newInstance();
		} catch (Exception e) {
			logger.error(e);
		}
	}
	
	public Connection getConnection() throws SQLException{
		Connection connection = null;
		connection = DriverManager.getConnection(url+dbName,userName,password);
		connection.setAutoCommit(false);
		return connection;
	}
	
	public String executeQuery(String sqlQuery) throws SQLException, SQLExceptionNoChange {
		Connection connection = null;
		Statement pst = null;
		ResultSet rs = null;
		String id = "";
		Integer idGenerado = 0;
		int transaction = 0;
		try{
			connection = getConnection();			
			pst = connection.createStatement();
			transaction = pst.executeUpdate(sqlQuery,Statement.RETURN_GENERATED_KEYS);
			
			if(transaction<=0){
				throw new SQLExceptionNoChange("El execute no realizo ningun cambio");
			}
			connection.commit();
			rs = pst.getGeneratedKeys();
	        if (rs.next()){
	        	idGenerado=rs.getInt(1);
	        	id = idGenerado.toString();
	        }
//			id = getLastInsertId();
//		}catch (Exception e) {
//			logger.error(e);
		}finally{
			closeConnections(connection,pst,transaction,rs);
		}
		return id;
	}

	

	

	protected String getLastInsertId() throws SQLException {
		Connection 			connection = null;
		PreparedStatement 	pst = null;
		ResultSet			result = null;
		String				id = "NULL";
		String 				sqlQuery = "";
		try{
			sqlQuery = "SELECT LAST_INSERT_ID()";
			connection = getConnection();			
			pst = connection.prepareStatement(sqlQuery);
			result = pst.executeQuery();
			if(result!=null && result.next()){
				Integer resultId = null;
				resultId = result.getInt(1);
				if(resultId!=null){
					id = resultId.toString();
				}
			}
//		}catch (Exception e) {
//			logger.error(e);
		}finally{
			closeConnections(connection,pst,result);
		}
		return id;
	}
	public String getIdQuery(String sqlQuery) throws SQLException, SQLExceptionNoChange {
		Connection 			connection = null;
		PreparedStatement 	pst = null;
		ResultSet			result = null;
		String				id = "NULL";
		try{
			connection = getConnection();			
			pst = connection.prepareStatement(sqlQuery);
			result = pst.executeQuery();
			if(result!=null && result.next()){
				Integer resultId = null;
				resultId = result.getInt("id");
				if(resultId!=null){
					id = resultId.toString();
				}
			}
//		}catch (Exception e) {
//			logger.error(e);
		}finally{
			closeConnections(connection,pst,result);
		}
		return id;
	}
	private void closeConnections(Connection connection, PreparedStatement pst,int transaction) {
		commitTransaction(connection,transaction);
		closePrepareStatement(pst);
		closeConnection(connection);
	}
	private void closeConnections(Connection connection, PreparedStatement pst,
			int transaction, ResultSet result) {
		commitTransaction(connection,transaction);
		closeResultSet(result);
		closePrepareStatement(pst);
		closeConnection(connection);
	}
	private void closeConnections(Connection connection, Statement pst,
			int transaction, ResultSet result) {
//		commitTransaction(connection,transaction);
		closeResultSet(result);
		closePrepareStatement(pst);
		closeConnection(connection);
	}
	private void closeConnections(Connection connection, PreparedStatement pst,ResultSet result) {		
		closeResultSet(result);
		closePrepareStatement(pst);
		closeConnection(connection);
	}
	
	protected void closePrepareStatement(PreparedStatement preparedStatement){
		try {
			if(preparedStatement!=null){
				preparedStatement.close();
			}
		} catch (Exception e) {
			logger.error(e);
		}
	}
	protected void closePrepareStatement(Statement preparedStatement){
		try {
			if(preparedStatement!=null){
				preparedStatement.close();
			}
		} catch (Exception e) {
			logger.error(e);
		}
	}
	protected void commitTransaction(Connection connection, int transaccion){
		try {
			if(connection!=null && transaccion>0){
				connection.commit();
			}
		} catch (Exception e) {
			logger.error(e);
		}
	}
	protected void closeConnection(Connection connection){
		try {
			if(connection!=null){
				connection.close();
			}
		} catch (Exception e) {
			logger.error(e);
		}
	}
	protected void closeResultSet(ResultSet result){
		try {
			if(result!=null){
				result.close();
			}
		} catch (Exception e) {
			logger.error(e);
		}
	}
	protected String	escapeValue(String dato){
		String out = "";
		if(dato==null){
			dato = "";
		}
		if(dato.equals("null")){
			dato = dato.replace("null", "");
		}
//		out=StringEscapeUtils.unescapeJava(dato);
		out=StringEscapeUtils.escapeHtml(dato);
		out=unescapeHtml(dato);
		out=escapeSql(out);
//		out = StringEscapeUtils.unescapeXml(out);
		return out;
	}
	protected String escapeSql(String data){
		return StringEscapeUtils.escapeSql(data);
	}
	protected String unescapeHtml(String data){
		return StringEscapeUtils.unescapeHtml(data);
	}
	
	protected String getSQLPaisId(String pais){
		String sql = "";
		sql ="(SELECT id FROM ubicacion_pais where UPPER(nombre)=UPPER('"+escapeValue(pais)+"'))";
		return sql;
	}
	protected String getSQLProvinciaId(String pais,String provincia){
		String sql = "";
		sql ="(SELECT id FROM ubicacion_provincia where UPPER(nombre)=UPPER('"+escapeValue(provincia)+"') AND fk_pais_id = "+getSQLPaisId(pais)+" )";
		return sql;
	}
	protected String getSQLPartidoId(String pais,String provincia,String partido){
		String sql = "";
		sql =" (SELECT id FROM ubicacion_partido where UPPER(nombre)=UPPER('"+escapeValue(partido)+"') AND fk_provincia_id IN "+getSQLProvinciaId(pais,provincia)+")";
		return sql;
	}
	
	protected String getSQLLocalidadId(String pais,String provincia,String partido,String localidad){
		String sql = "";
		sql ="(SELECT id FROM ubicacion_localidad where UPPER(nombre) LIKE UPPER('"+escapeValue(localidad)+"') AND fk_partido_id = "+getSQLPartidoId(pais,provincia,partido)+")";
		return sql;
	}
}
